package com.gjzt.business.media.service;

import java.sql.*;
import java.util.Date;

import com.eastdigit.util.PropertiesUtil;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eastdigit.servlet.ReqBean;
import com.eastdigit.system.service.BaseService;
import com.eastdigit.util.DateUtil;
import com.eastdigit.util.StringUtil;
import com.gjzt.business.media.inter.MediaBusMapper;
import com.gjzt.business.media.model.MediaBus;

@Service
public class MediaBusService extends BaseService {

    @Autowired
    private MediaBusMapper mediaBusMapper;

    @Autowired
    private MediaResourceService mediaResourceService;

    public static String DRIVER_NAME = PropertiesUtil.getProperty("sqlServer.driverName");

    public static String DB_URL = PropertiesUtil.getProperty("sqlServer.dbURL");

    public static String USERNAME = PropertiesUtil.getProperty("sqlServer.userName");

    public static String PASSWORD = PropertiesUtil.getProperty("sqlServer.password");

    /**
     * 查询
     * 
     * @param reqBean
     * @return
     */
    public JSONObject findMediaBus(ReqBean reqBean) {
        StringBuffer sql = new StringBuffer("select b.*,b.BUS_ID itemId from MEDIA_BUS b where 1 = 1 ");
        if (!StringUtil.isEmpty(reqBean.getInfo("vehicleNo"))) {
            sql.append(" and b.VEHICLE_NO = '").append(reqBean.getInfo("vehicleNo")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("vehicleNoAbbr"))) {
            sql.append(" and b.VEHICLE_NO_ABBR = '").append(reqBean.getInfo("vehicleNoAbbr")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("plateNo"))) {
            sql.append(" and b.PLATE_NO = '").append(reqBean.getInfo("plateNo")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("lineNo"))) {
            sql.append(" and b.LINE_NO = '").append(reqBean.getInfo("lineNo")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("companyId"))) {
            sql.append(" and b.COMPANY_ID = '").append(reqBean.getInfo("companyId")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("busType"))) {
            sql.append(" and b.BUS_TYPE = '").append(reqBean.getInfo("busType")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("busFloor"))) {
            sql.append(" and b.BUS_FLOOR = '").append(reqBean.getInfo("busFloor")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("airConditioned"))) {
            sql.append(" and b.AIR_CONDITIONED = '").append(reqBean.getInfo("airConditioned")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("busEnergy"))) {
            sql.append(" and b.BUS_ENERGY = '").append(reqBean.getInfo("busEnergy")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("businessType"))) {
            sql.append(" and b.BUSINESS_TYPE = '").append(reqBean.getInfo("businessType")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("status"))) {
            sql.append(" and b.STATUS = '").append(reqBean.getInfo("status")).append("'");
        }
        sql.append(" order by b.COMPANY_NAME, b.LINE_NO , b.PLATE_NO");
        // if(StringUtils.isBlank(reqBean.getInfo("pageSize"))) {
        // return this.queryTable(sql.toString(),null,reqBean.getPageSize(),
        // reqBean.getPageIndex());
        // } else {
        // return
        // this.queryTable(sql.toString(),null,Integer.parseInt(reqBean.getInfo("pageSize")),
        // reqBean.getPageIndex());
        // }
        return this.queryTable(sql.toString(), null, reqBean.getPageSize(), reqBean.getPageIndex());

//        Connection conn = null;
//        PreparedStatement pstmt = null;
//        ResultSet rs = null;
//        if (returnJson != null) {
//            JSONArray datas = returnJson.getJSONArray("rows");
//            if (datas != null && datas.size() > 0) {
//                try {
//                    Class.forName(DRIVER_NAME);
//                    conn = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD);
//                    for (int i = 0; i < datas.size(); i++) {
//                        JSONObject o = datas.getJSONObject(i);
//                        String lineNo = o.getString("lineNo");
//                        String statusSql = "SELECT O_ZKGLEX zkgl FROM dbo.Tbl_OP_Rundynamic_Main WHERE O_XLMC = '532' AND O_RQ = '2017-05-24'";
//
//                    }
//
//                } catch (Exception e) {
//                    Logger.getLogger(this.getClass()).error(e.getMessage(), e);
//                } finally {
//                    try {
//                        rs.close();
//                    } catch (SQLException e) {
//                        Logger.getLogger(this.getClass()).error(e.getMessage(), e);
//                    }
//                    try {
//                        pstmt.close();
//                    } catch (SQLException e) {
//                        Logger.getLogger(this.getClass()).error(e.getMessage(), e);
//                    }
//                    try {
//                        conn.close();
//                    } catch (SQLException e) {
//                        Logger.getLogger(this.getClass()).error(e.getMessage(), e);
//                    }
//                }
//            }
//        }
//
//        return returnJson;
    }

    public JSONObject getMediaBusInfo(String busId) {
        MediaBus bus = getMediaBus(busId);
        return bus == null ? null : bus.toJsonObject();
    }

    public JSONObject getBusInfoWithRes(String busId) {
        JSONObject returnJson = new JSONObject();
        JSONObject busInfo = getMediaBusInfo(busId);
        JSONArray busResources = mediaResourceService.findBusMediaResources(busId);
        returnJson.put("busInfo", busInfo);
        returnJson.put("busResources", busResources);
        return returnJson;
    }

    public JSONArray queryCompanys() {
        StringBuffer sql = new StringBuffer(
                " select DISTINCT COMPANY_ID, COMPANY_NAME from MEDIA_BUS order by COMPANY_NAME ");
        return this.query(sql.toString());
    }

    public MediaBus getMediaBus(String busId) {
        return mediaBusMapper.selectByPrimaryKey(busId);
    }

    /**
     * 保存
     * 
     * @param reqBean
     */
    public void saveMediaBus(ReqBean reqBean) {
        MediaBus mediaBus = getMediaBus(reqBean);

        mediaBus.setOperator(reqBean.getUserId());
        mediaBus.setModifyTime(new Date());

        mediaBusMapper.updateBusInfo(mediaBus);

    }

    /**
     * 从表单获取对象
     * 
     * @param reqBean
     * @return
     */
    private MediaBus getMediaBus(ReqBean reqBean) {
        MediaBus mediaBus = new MediaBus();

        mediaBus.setBusId(reqBean.getInfo("busId"));
        mediaBus.setVehicleNo(reqBean.getInfo("vehicleNo"));
        mediaBus.setVehicleNoAbbr(reqBean.getInfo("vehicleNoAbbr"));
        mediaBus.setPlateNo(reqBean.getInfo("plateNo"));
        mediaBus.setLineId(reqBean.getInfo("lineId"));
        mediaBus.setLineNo(reqBean.getInfo("lineNo"));
        mediaBus.setCompanyId(reqBean.getInfo("companyId"));
        mediaBus.setCompanyName(reqBean.getInfo("companyName"));
        mediaBus.setBusType(reqBean.getInfo("busType"));
        mediaBus.setBusFloor(reqBean.getInfo("busFloor"));
        mediaBus.setAirConditioned(reqBean.getInfo("airConditioned"));
        mediaBus.setBusEnergy(reqBean.getInfo("busEnergy"));
        mediaBus.setBusinessType(reqBean.getInfo("businessType"));
        mediaBus.setOnlineDate(reqBean.getInfo("onlineDate"));
        mediaBus.setBusImg(reqBean.getInfo("busImg"));
        mediaBus.setStatus(reqBean.getInfo("status"));
        mediaBus.setOperator(reqBean.getUserId());
        mediaBus.setMakeTime(DateUtil.parseDateTime(reqBean.getInfo("makeTime")));
        mediaBus.setModifyTime(new Date());
        mediaBus.setRemark(reqBean.getInfo("remark"));

        return mediaBus;
    }
}